﻿"""
分数数据与CF系数处理模块
"""
# 导入需要的库
import pandas as pd
from datetime import datetime
from sqlalchemy import create_engine
from decimal import Decimal

# 创建SQL server 数据库连接
engine = create_engine(
    "mssql+pyodbc://pereader:pereader@172.18.65.31,1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server")
# 显示所有的列 方便调试
pd.set_option('display.max_columns', None)
pd.set_option('display.float_format', lambda x: '%.6f' % x)


def get_ID(item_code):
    ID = pd.read_sql(f"SELECT FEATURE_VALUE FROM ITEM_CODE_VALUE WHERE ITEM_CODE='{item_code}' "
                     f"AND FEATURE_NAME='标准件型号'", engine)
    if not ID.empty:
        return ID.loc[0, 'FEATURE_VALUE']
    else:
        return ''


def get_insert_sql(table_name, df):
    colnames = '[' + '],['.join(list(df.columns)) + ']'
    # print(colnames)
    values_lis = []

    def func1(sr):
        value_str = "("
        for value in list(sr.values):
            # value = str(value)
            # value = value
            if str(value).strip(' ') and value != 'None' and str(value).strip(' ') != 'nan' and value != 'NaT':
                if type(value) == type(1.0):
                    # print(value)
                    value_str = value_str + str(value) + ","
                else:
                    value_str = value_str + "'" + str(value).strip(' ') + "',"
            else:
                value_str = value_str + 'Null' + ","
        value_str = value_str[: -1] + ")"
        values_lis.append(value_str)

    sql = ""
    df.apply(func1, axis=1)
    for i in range(0, len(values_lis) // 500 + 1):
        if len(values_lis) > (i + 1) * 500:
            values_str = ','.join(values_lis[i * 500: (i + 1) * 500])
        else:
            values_str = ','.join(values_lis[i * 500:])
        sql = sql + f"insert into {table_name} ({colnames}) values {values_str};"
    # values_str = ','.join(values_lis)
    # sql = f"insert into {self.table_name} ({colnames}) values {values_str};"
    # print(sql)
    # with open(main_filepath + r'\测试\sql.txt', 'w', encoding='utf-8') as f:
    #     f.write(sql)
    return sql


# 根据工单获取品号
def get_item_code(MoID):
    item_code = pd.read_sql(
        f"SELECT top 1 ITEM_CODE FROM MO_ITEM WHERE DOC_NO='{MoID}';", engine)
    if not item_code.empty:
        item_code1 = item_code.loc[0, 'ITEM_CODE']
    else:
        item_code1 = ''
    return item_code1


# CF2,CF3参数获取
def get_cf(item_code, cf, CCT='low'):
    """
    :param item_code:品号
    :param cf: 2为CF2,3为CF3
    :return: 数据状态与dataframe
    """
    if CCT == 'low':
        cf_sel = {
            2: ('CF2-调整系数代码', 'CF2_LEDteen', 'CF2_Code'),
            3: ('CF3-对标系数代码', 'CF3_Customer', 'CF3_Code')
        }
    else:
        cf_sel = {
            2: ('CF2-调整系数代码-高色温', 'CF2_LEDteen', 'CF2_Code'),
            3: ('CF3-对标系数代码-高色温', 'CF3_Customer', 'CF3_Code')
        }
    if cf == 2 or cf == 3:
        df_CF = pd.read_sql(
            f"SELECT * FROM ITEM_CODE_VALUE,{cf_sel[cf][1]} WHERE ITEM_CODE='{item_code}' "
            f"AND FEATURE_NAME='{cf_sel[cf][0]}' AND DataStatus=1 AND "
            f"ITEM_CODE_VALUE.FEATURE_VALUE={cf_sel[cf][1]}.{cf_sel[cf][2]} "
            "collate Chinese_PRC_CI_AS", engine)
    else:
        df_CF = pd.DataFrame()
    return df_CF


# 标准件生效数据获取
def get_standpart_data(item_code, CCT_String=None):
    """

    :param item_code:
    :return:
    """
    if CCT_String:
        df_standpart_data = pd.read_sql(
            "SELECT T1.ITEM_CODE,T1.ITEM_NAME,T2.StandardPartID,T2.StandardPartNO,T2.Current_mA AS I_y,"
            "T2.ForwardVoltage_V AS U_y,T2.CIEx AS x_y,T2.CIEY AS y_y,T2.LuminousFlux_lm AS lm_y, "
            "T2.Ra AS Ra_y,T2.R9 AS R9_y FROM ITEM_CODE_VALUE as T1, StandardPartData as T2 "
            f"WHERE T1.FEATURE_VALUE+'{CCT_String}'=T2.StandardPartID collate Chinese_PRC_CI_AS "
            f"AND T1.FEATURE_NAME='标准件型号' AND T1.ITEM_CODE='{item_code}' AND T2.DataStatus=1;",
            engine)
    else:
        df_standpart_data = pd.read_sql(
            "SELECT T1.ITEM_CODE,T1.ITEM_NAME,T2.StandardPartID,T2.StandardPartNO,T2.Current_mA AS I_y,"
            "T2.ForwardVoltage_V AS U_y,T2.CIEx AS x_y,T2.CIEY AS y_y,T2.LuminousFlux_lm AS lm_y, "
            "T2.Ra AS Ra_y,T2.R9 AS R9_y FROM ITEM_CODE_VALUE as T1, StandardPartData as T2 "
            "WHERE T1.FEATURE_VALUE=T2.StandardPartID collate Chinese_PRC_CI_AS "
            f"AND T1.FEATURE_NAME='标准件型号' AND T1.ITEM_CODE='{item_code}' AND T2.DataStatus=1;",
            engine)
    # 除去重复数据
    df_standpart_data.drop_duplicates(subset='StandardPartNO', keep='first', inplace=True)
    return df_standpart_data


# 测试机数据提取转换方法
def get_test_data(file_path, machine_name, logger):
    df_test_data = pd.DataFrame()
    error_string = ''
    try:
        if "本地测试" in machine_name:
            df_test_data = pd.read_csv(
                filepath_or_buffer=file_path,
                encoding='gbk',
                sep='\t',
                header=0,
                usecols=[
                    '序号', '时间', 'I(A)', 'U(V)', 'P(W)', 'Φ(lm)', 'Φe(mW)',
                    '光效(lm/W)', 'x', 'y', "u'", "v'", 'CCT(K)', '主波长(nm)',
                    '峰值波长(nm)', '半峰带宽(nm)', 'Ra', 'R1', 'R2', 'R3', 'R4', 'R5',
                    'R6', 'R7', 'R8', 'R9', 'R10', 'R11', 'R12', 'R13', 'R14',
                    'R15', 'SDCM', 'Tb'
                ])

            df_test_data = df_test_data.rename(
                columns={'序号': 'TestNO', '时间': 'TestTime', 'I(A)': 'Current_mA', 'U(V)': 'ForwardVoltage_V',
                         'P(W)': 'Power_W',
                         'Φ(lm)': 'LuminousFlux_lm', 'Φe(mW)': 'RadiantFlux_mW', '光效(lm/W)': 'LumiousEfficacy_lmPerW',
                         'x': 'CIEx', 'y': 'CIEy', "u'": 'CIEu_1976', "v'": 'CIEv_1976', 'CCT(K)': 'CCT_K',
                         '主波长(nm)': 'DominantWavelength_nm', '峰值波长(nm)': 'PeakWavelength_nm', '半峰带宽(nm)': 'FWHM_nm',
                         'Ra': 'Ra', 'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5',
                         'R6': 'R6', 'R7': 'R7', 'R8': 'R8', 'R9': 'R9', 'R10': 'R10', 'R11': 'R11', 'R12': 'R12',
                         'R13': 'R13',
                         'R14': 'R14', 'R15': 'R15', 'SDCM': 'SDCM', 'Tb': 'TestTemperature'})
            df_test_data['Current_mA'] = df_test_data['Current_mA'] * 1000
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '多谱分光机':
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='utf_16', sep='\t', header=6,
                                       usecols=['NO', 'BIN号', 'VF', 'Φv', 'CIE-x', 'CIE-y', 'Ra', 'Tc', 'λd', 'λp',
                                                "CIE-u'", "CIE-v'", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8',
                                                'R9', 'R10', 'R11', 'R12', 'R13', 'R14', 'R15', 'IF', 'Pow', 'SDCM',
                                                'BIN名',
                                                '时间',
                                                '光功率', '光效'])
            # 输出未处理的结果，方便代码调试
            df_test_data['光功率'] = df_test_data['光功率'] * 1000
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(
                columns={'NO': 'TestNO', 'BIN号': 'BinID', 'VF': 'ForwardVoltage_V', 'Φv': 'LuminousFlux_lm',
                         'CIE-x': 'CIEx', 'CIE-y': 'CIEy',
                         'Ra': 'Ra', 'Tc': 'CCT_K', 'λd': 'DominantWavelength_nm', 'λp': 'PeakWavelength_nm',
                         "CIE-u'": 'CIEu_1976', "CIE-v'": 'CIEv_1976',
                         'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7',
                         'R8': 'R8',
                         'R9': 'R9', 'R10': 'R10', 'R11': 'R11',
                         'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15', 'IF': 'Current_mA',
                         'Pow': 'Power_W', 'SDCM': 'SDCM', 'BIN名': 'BinName', '时间': 'TestTime',
                         '光功率': 'RadiantFlux_mW',
                         '光效': 'LumiousEfficacy_lmPerW'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '中为排测机':
            # 读取csv格式文件，按分光机002输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=0,
                                       usecols=['  NO', 'Tc(k)', 'λd(nm)', 'λp(nm)',
                                                '△λ', 'CIE-x', 'CIE-y', 'Ra',
                                                'фv(lm)', 'R9', 'P(mw)', 'Vf(V)',
                                                'I(mA)', 'P(w)', 'Efficiency(lm/w)',
                                                'SDCM', 'BIN NO.', 'BIN name',
                                                'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8',
                                                'R10', 'R11',
                                                'R12', 'R13', 'R14', 'R15', 'Time'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(
                columns={'  NO': 'TestNO', 'Tc(k)': 'CCT_K', 'λd(nm)': 'DominantWavelength_nm',
                         'λp(nm)': 'PeakWavelength_nm', '△λ': 'FWHM_nm', 'CIE-x': 'CIEx', 'CIE-y': 'CIEy',
                         'Ra': 'Ra',
                         'фv(lm)': 'LuminousFlux_lm', 'R9': 'R9', 'P(mw)': 'RadiantFlux_mW',
                         'Vf(V)': 'ForwardVoltage_V',
                         'I(mA)': 'Current_mA', 'P(w)': 'Power_W', 'Efficiency(lm/w)': 'LumiousEfficacy_lmPerW',
                         'SDCM': 'SDCM', 'BIN NO.': 'BinID', 'BIN name': 'BinName',
                         'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7',
                         'R8': 'R8',
                         'R10': 'R10', 'R11': 'R11',
                         'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15', 'Time': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '中谱连片分光机':
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=0,
                                       usecols=['No.', 'Bin号', 'Bin代号',
                                                'IF', 'VF', 'Ф(lm)',
                                                'η(lm/W)', 'CIE-x', 'CIE-y',
                                                'Tc', 'Ra', 'CRI9', 'SDCM',
                                                'P(W)', 'WL.D', 'CIE-u', 'CIE-v',
                                                "CIE-u'", "CIE-v'",
                                                'WL.P', 'WL.C',
                                                'WL.H', 'Фe(mW)', 'CRI1', 'CRI2',
                                                'CRI3', 'CRI4', 'CRI5', 'CRI6',
                                                'CRI7', 'CRI8', 'CRI10', 'CRI11',
                                                'CRI12', 'CRI13', 'CRI14', 'CRI15', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'No.': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName',
                                                        'IF': 'Current_mA', 'VF': 'ForwardVoltage_V',
                                                        'Ф(lm)': 'LuminousFlux_lm',
                                                        'η(lm/W)': 'LumiousEfficacy_lmPerW', 'CIE-x': 'CIEx',
                                                        'CIE-y': 'CIEy',
                                                        'Tc': 'CCT_K', 'Ra': 'Ra', 'CRI9': 'R9', 'SDCM': 'SDCM',
                                                        'P(W)': 'Power_W', 'WL.D': 'DominantWavelength_nm',
                                                        'CIE-u': 'CIEu',
                                                        'CIE-v': 'CIEv',
                                                        "CIE-u'": 'CIEu_1976', "CIE-v'": 'CIEv_1976',
                                                        'WL.P': 'PeakWavelength_nm',
                                                        'WL.C': 'ComplementaryWavelength_nm',
                                                        'WL.H': 'FWHM_nm', 'Фe(mW)': 'RadiantFlux_mW', 'CRI1': 'R1',
                                                        'CRI2': 'R2',
                                                        'CRI3': 'R3', 'CRI4': 'R4', 'CRI5': 'R5', 'CRI6': 'R6',
                                                        'CRI7': 'R7', 'CRI8': 'R8', 'CRI10': 'R10', 'CRI11': 'R11',
                                                        'CRI12': 'R12', 'CRI13': 'R13', 'CRI14': 'R14', 'CRI15': 'R15',
                                                        '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '中谱单颗分光机':
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            # '编号','Bin代号','编号': 'TestNO', 'Bin代号': 'BinName',
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=13,
                                       usecols=['No.', 'Bin号', 'Bin代号',
                                                '温度', 'AOI结果', 'C1:VF',
                                                'C1:IF', 'C1:P(W)', 'C1:x', 'C1:y',
                                                'C1:Ф(lm)', 'C1:λd(nm)',
                                                'C1:Tc', 'C1:Ra', 'C1:u', 'C1:v', "C1:u'", "C1:v'",
                                                'C1:λp(nm)', 'C1:λc(nm)', 'C1:λh(nm)',
                                                'C1:Фe(mW)', 'C1:η(lm/W)', 'C1:SDCM',
                                                'C1:CRI1', 'C1:CRI2', 'C1:CRI3', 'C1:CRI4', 'C1:CRI5', 'C1:CRI6',
                                                'C1:CRI7', 'C1:CRI8', 'C1:CRI9', 'C1:CRI10', 'C1:CRI11',
                                                'C1:CRI12', 'C1:CRI13', 'C1:CRI14', 'C1:CRI15', 'C1:VF1', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'No.': 'TestNO', 'Bin代号': 'BinName', 'Bin号': 'BinID',
                                                        '温度': 'TestTemperature', 'AOI结果': 'AoiResult',
                                                        'C1:VF': 'ForwardVoltage_V',
                                                        'C1:IF': 'Current_mA', 'C1:P(W)': 'Power_W', 'C1:x': 'CIEx',
                                                        'C1:y': 'CIEy',
                                                        'C1:Ф(lm)': 'LuminousFlux_lm',
                                                        'C1:λd(nm)': 'DominantWavelength_nm',
                                                        'C1:Tc': 'CCT_K', 'C1:Ra': 'Ra', 'C1:u': 'CIEu', 'C1:v': 'CIEv',
                                                        "C1:u'": 'CIEu_1976',
                                                        "C1:v'": 'CIEv_1976',
                                                        'C1:λp(nm)': 'PeakWavelength_nm',
                                                        'C1:λc(nm)': 'ComplementaryWavelength_nm',
                                                        'C1:λh(nm)': 'FWHM_nm',
                                                        'C1:Фe(mW)': 'RadiantFlux_mW',
                                                        'C1:η(lm/W)': 'LumiousEfficacy_lmPerW',
                                                        'C1:SDCM': 'SDCM',
                                                        'C1:CRI1': 'R1', 'C1:CRI2': 'R2', 'C1:CRI3': 'R3',
                                                        'C1:CRI4': 'R4',
                                                        'C1:CRI5': 'R5',
                                                        'C1:CRI6': 'R6',
                                                        'C1:CRI7': 'R7', 'C1:CRI8': 'R8', 'C1:CRI9': 'R9',
                                                        'C1:CRI10': 'R10', 'C1:CRI11': 'R11',
                                                        'C1:CRI12': 'R12', 'C1:CRI13': 'R13', 'C1:CRI14': 'R14',
                                                        'C1:CRI15': 'R15',
                                                        'C1:VF1': 'ZenerVoltage_V', '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '中谱分光一体机#5':
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=15,
                                       usecols=['编号', 'Bin号', 'Bin代号', '温度', 'AOI结果', 'C1:VF1', 'C1:VF', 'C1:IF',
                                                'C1:P(W)',
                                                'C1:x', 'C1:y', 'C1:Ф(lm)', 'C1:λd(nm)',
                                                'C1:Tc', 'C1:Ra', 'C1:u', 'C1:v', "C1:u'", "C1:v'",
                                                'C1:λp(nm)', 'C1:λc(nm)', 'C1:λh(nm)',
                                                'C1:Фe(mW)', 'C1:η(lm/W)', 'C1:SDCM',
                                                'C1:CRI1', 'C1:CRI2', 'C1:CRI3', 'C1:CRI4', 'C1:CRI5', 'C1:CRI6',
                                                'C1:CRI7', 'C1:CRI8', 'C1:CRI9', 'C1:CRI10', 'C1:CRI11',
                                                'C1:CRI12', 'C1:CRI13', 'C1:CRI14', 'C1:CRI15', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df_test_data)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'编号': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName',
                                                        '温度': 'TestTemperature', 'AOI结果': 'AoiResult',
                                                        'C1:VF1': 'ZenerVoltage_V',
                                                        'C1:VF': 'ForwardVoltage_V',
                                                        'C1:IF': 'Current_mA', 'C1:P(W)': 'Power_W', 'C1:x': 'CIEx',
                                                        'C1:y': 'CIEy',
                                                        'C1:Ф(lm)': 'LuminousFlux_lm',
                                                        'C1:λd(nm)': 'DominantWavelength_nm',
                                                        'C1:Tc': 'CCT_K', 'C1:Ra': 'Ra', 'C1:u': 'CIEu', 'C1:v': 'CIEv',
                                                        "C1:u'": 'CIEu_1976',
                                                        "C1:v'": 'CIEv_1976',
                                                        'C1:λp(nm)': 'PeakWavelength_nm',
                                                        'C1:λc(nm)': 'ComplementaryWavelength_nm',
                                                        'C1:λh(nm)': 'FWHM_nm',
                                                        'C1:Фe(mW)': 'RadiantFlux_mW',
                                                        'C1:η(lm/W)': 'LumiousEfficacy_lmPerW',
                                                        'C1:SDCM': 'SDCM',
                                                        'C1:CRI1': 'R1', 'C1:CRI2': 'R2', 'C1:CRI3': 'R3',
                                                        'C1:CRI4': 'R4',
                                                        'C1:CRI5': 'R5',
                                                        'C1:CRI6': 'R6',
                                                        'C1:CRI7': 'R7', 'C1:CRI8': 'R8', 'C1:CRI9': 'R9',
                                                        'C1:CRI10': 'R10', 'C1:CRI11': 'R11',
                                                        'C1:CRI12': 'R12', 'C1:CRI13': 'R13', 'C1:CRI14': 'R14',
                                                        'C1:CRI15': 'R15',
                                                        '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])
        elif machine_name == '中谱分光一体机#6':
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=18,
                                       usecols=['编号', 'Bin号', 'Bin代号', '温度', 'AOI结果', 'C1:VF', 'C1:IF',
                                                'C1:P(W)',
                                                'C1:x', 'C1:y', 'C1:Ф(lm)', 'C1:λd(nm)',
                                                'C1:Tc', 'C1:Ra', 'C1:u', 'C1:v', "C1:u'", "C1:v'",
                                                'C1:λp(nm)', 'C1:λc(nm)', 'C1:λh(nm)',
                                                'C1:Фe(mW)', 'C1:η(lm/W)', 'C1:SDCM',
                                                'C1:CRI1', 'C1:CRI2', 'C1:CRI3', 'C1:CRI4', 'C1:CRI5', 'C1:CRI6',
                                                'C1:CRI7', 'C1:CRI8', 'C1:CRI9', 'C1:CRI10', 'C1:CRI11',
                                                'C1:CRI12', 'C1:CRI13', 'C1:CRI14', 'C1:CRI15', 'C1:VF1', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df_test_data)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'编号': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName',
                                                        '温度': 'TestTemperature', 'AOI结果': 'AoiResult',
                                                        'C1:VF1': 'ZenerVoltage_V',
                                                        'C1:VF': 'ForwardVoltage_V',
                                                        'C1:IF': 'Current_mA', 'C1:P(W)': 'Power_W', 'C1:x': 'CIEx',
                                                        'C1:y': 'CIEy',
                                                        'C1:Ф(lm)': 'LuminousFlux_lm',
                                                        'C1:λd(nm)': 'DominantWavelength_nm',
                                                        'C1:Tc': 'CCT_K', 'C1:Ra': 'Ra', 'C1:u': 'CIEu', 'C1:v': 'CIEv',
                                                        "C1:u'": 'CIEu_1976',
                                                        "C1:v'": 'CIEv_1976',
                                                        'C1:λp(nm)': 'PeakWavelength_nm',
                                                        'C1:λc(nm)': 'ComplementaryWavelength_nm',
                                                        'C1:λh(nm)': 'FWHM_nm',
                                                        'C1:Фe(mW)': 'RadiantFlux_mW',
                                                        'C1:η(lm/W)': 'LumiousEfficacy_lmPerW',
                                                        'C1:SDCM': 'SDCM',
                                                        'C1:CRI1': 'R1', 'C1:CRI2': 'R2', 'C1:CRI3': 'R3',
                                                        'C1:CRI4': 'R4',
                                                        'C1:CRI5': 'R5',
                                                        'C1:CRI6': 'R6',
                                                        'C1:CRI7': 'R7', 'C1:CRI8': 'R8', 'C1:CRI9': 'R9',
                                                        'C1:CRI10': 'R10', 'C1:CRI11': 'R11',
                                                        'C1:CRI12': 'R12', 'C1:CRI13': 'R13', 'C1:CRI14': 'R14',
                                                        'C1:CRI15': 'R15',
                                                        '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])
        elif 'ZP' in machine_name:
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(
                filepath_or_buffer=file_path,
                encoding='gbk',
                header=0,
                usecols=[
                    '选择', 'No.', 'Bin号', 'Bin代号', 'VF', 'IF', 'P(W)', 'CIE-x',
                    'CIE-y', 'Tc', 'WL.D', 'Ф(lm)', 'Ra', 'CIE-u', 'CIE-v',
                    "CIE-u'", "CIE-v'", 'WL.P', 'WL.C', 'WL.H', 'Фe(mW)',
                    'η(lm/W)', 'SDCM', 'CRI1', 'CRI2', 'CRI3', 'CRI4', 'CRI5',
                    'CRI6', 'CRI7', 'CRI8', 'CRI9', 'CRI10', 'CRI11', 'CRI12',
                    'CRI13', 'CRI14', 'CRI15', '测试时间'
                ])
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'选择': 'Select', 'No.': 'TestNO',
                                                        'Bin号': 'BinID', 'Bin代号': 'BinName',
                                                        'VF': 'ForwardVoltage_V', 'IF': 'Current_mA', 'P(W)': 'Power_W',
                                                        'CIE-x': 'CIEx', 'CIE-y': 'CIEy', 'Tc': 'CCT_K',
                                                        'WL.D': 'DominantWavelength_nm', 'Ф(lm)': 'LuminousFlux_lm',
                                                        'Ra': 'Ra',
                                                        'CIE-u': 'CIEu', 'CIE-v': 'CIEv', "CIE-u'": 'CIEu_1976',
                                                        "CIE-v'": 'CIEv_1976',
                                                        'WL.P': 'PeakWavelength_nm',
                                                        'WL.C': 'ComplementaryWavelength_nm',
                                                        'WL.H': 'FWHM_nm',
                                                        'Фe(mW)': 'RadiantFlux_mW', 'η(lm/W)': 'LumiousEfficacy_lmPerW',
                                                        'SDCM': 'SDCM',
                                                        'CRI1': 'R1', 'CRI2': 'R2', 'CRI3': 'R3', 'CRI4': 'R4',
                                                        'CRI5': 'R5', 'CRI6': 'R6',
                                                        'CRI7': 'R7', 'CRI8': 'R8', 'CRI9': 'R9', 'CRI10': 'R10',
                                                        'CRI11': 'R11',
                                                        'CRI12': 'R12', 'CRI13': 'R13', 'CRI14': 'R14', 'CRI15': 'R15',
                                                        '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '远方1号机':
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', sep='\t', header=0,
                                       usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "测试时间",
                                                "x",
                                                "y", "CCT(K)",
                                                "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7',
                                                'R8',
                                                'R9',
                                                'R10', 'R11', 'R12', 'R13', 'R14', 'R15', '环境温度'])
            df_test_data = df_test_data.rename(
                columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W',
                         "Φ(lm)": 'LuminousFlux_lm',
                         "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW', "测试时间": 'TestTime',
                         "x": 'CIEx', "y": 'CIEy',
                         "CCT(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm', "峰值波长(nm)": 'PeakWavelength_nm',
                         "Ra": 'Ra', 'R1': 'R1',
                         'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8',
                         'R10': 'R10',
                         'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15',
                         '环境温度': 'TestTemperature'})
            df_test_data['Current_mA'] = df_test_data['Current_mA'] * 1000
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '远方2号机':
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', sep='\t', header=0,
                                       usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "测试时间",
                                                "x",
                                                "y", "Tc(K)",
                                                "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7',
                                                'R8',
                                                'R9',
                                                'R10', 'R11', 'R12', 'R13', 'R14', 'R15', '环境温度'])
            df_test_data = df_test_data.rename(
                columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W',
                         "Φ(lm)": 'LuminousFlux_lm',
                         "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW', "测试时间": 'TestTime',
                         "x": 'CIEx', "y": 'CIEy',
                         "Tc(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm', "峰值波长(nm)": 'PeakWavelength_nm',
                         "Ra": 'Ra', 'R1': 'R1',
                         'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8',
                         'R10': 'R10',
                         'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15',
                         '环境温度': 'TestTemperature'})
            df_test_data['Current_mA'] = df_test_data['Current_mA'] * 1000
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '远方3号机':
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', sep='\t', header=0,
                                       usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "时间", "x",
                                                "y",
                                                "CCT(K)",
                                                "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7',
                                                'R8',
                                                'R9',
                                                'R10', 'R11', 'R12', 'R13', 'R14', 'R15'])
            df_test_data = df_test_data.rename(
                columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W',
                         "Φ(lm)": 'LuminousFlux_lm',
                         "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW', "时间": 'TestTime',
                         "x": 'CIEx', "y": 'CIEy',
                         "CCT(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm', "峰值波长(nm)": 'PeakWavelength_nm',
                         "Ra": 'Ra', 'R1': 'R1',
                         'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8',
                         'R10': 'R10',
                         'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15'})
            df_test_data['Current_mA'] = df_test_data['Current_mA'] * 1000
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '中为积分球':
            df_test_data = pd.read_excel(io=file_path, sheet_name=0, header=0,
                                         usecols=["序号", "电流(mA)", "电压VF(V)", "功率(w)", "光通量(lm)", "光功率(mW)", "光效率(lm/w)",
                                                  "CIE-X", "CIE-Y", "BIN号", "色温(K)", "显色指数", "R9", "SDCM", "主波长(nm)",
                                                  "峰波长(nm)", "时间", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9',
                                                  'R10',
                                                  'R11', 'R12', 'R13', 'R14', 'R15'])
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(
                columns={"序号": 'TestNO', "电流(mA)": 'Current_mA', "电压VF(V)": 'ForwardVoltage_V', "功率(w)": 'Power_W',
                         "光通量(lm)": 'LuminousFlux_lm', "光功率(mW)": 'RadiantFlux_mW',
                         "光效率(lm/w)": 'LumiousEfficacy_lmPerW',
                         "CIE-X": 'CIEx', "CIE-Y": 'CIEy', "BIN号": 'BinName', "色温(K)": 'CCT_K', "显色指数": 'Ra',
                         "SDCM": 'SDCM',
                         "主波长(nm)": 'DominantWavelength_nm', "峰波长(nm)": 'PeakWavelength_nm', "时间": 'TestTime',
                         'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8',
                         'R10': 'R10', 'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

    except Exception as e:
        error_string = e
        logger.error(f"{machine_name},读取测试数据错误，描述:{e}")
    return df_test_data, error_string


# 数据加系数
def cal_coef(standpart_data, test_data):
    """
    :param standpart_data:
    :param test_data:
    :return:
    """
    df_cal_data1 = pd.DataFrame()
    try:
        # test_data = test_data[test_data['Current_mA']!=0]
        test_data = test_data.rename(
            columns={
                'TestNO': 'StandardPartNO',
                'Current_mA': 'I_x',
                'ForwardVoltage_V': 'U_x',
                'LuminousFlux_lm': 'lm_x',
                'CIEx': 'x_x',
                'CIEy': 'y_x',
                'Ra': 'Ra_x',
                'R9': 'R9_x'
            })
        test_data1 = test_data.loc[:, [
                                          'StandardPartNO', 'TestTime', 'I_x', 'U_x', 'lm_x', 'x_x', 'y_x',
                                          'Ra_x', 'R9_x'
                                      ]]
        # print(f"测试数据:{test_data1}")
        # print("*"*20)
        # print(standpart_data)
        df_cal_data = pd.merge(standpart_data,
                               test_data1,
                               how='inner',
                               left_on='StandardPartNO',
                               right_on='StandardPartNO')
        # print("*" * 20)
        # print(df_cal_data)
    except:
        df_cal_data = pd.DataFrame()
    if not df_cal_data.empty:
        # 生产参数列表
        pras = [['dU', 'U_y', 'U_x'], ['dx', 'x_y', 'x_x'],
                ['dy', 'y_y', 'y_x'], ['dRa', 'Ra_y', 'Ra_x'],
                ['dR9', 'R9_y', 'R9_x']]
        for i in pras:
            df_cal_data[i[0]] = df_cal_data[i[1]] - df_cal_data[i[2]]

        df_cal_data['dlm'] = df_cal_data['lm_y'] / df_cal_data['lm_x']
        df_cal_data['dI'] = (abs(df_cal_data['I_y'] - df_cal_data['I_x'])) / df_cal_data['I_y']
        df_cal_data1 = df_cal_data.loc[:, [
                                              'ITEM_CODE', 'StandardPartNO', 'dU', 'dx', 'dy', 'dRa', 'dR9',
                                              'dlm', 'dI'
                                          ]]
        pras = [['dU', 'U_y', 'U_x'], ['dx', 'x_y', 'x_x'],
                ['dy', 'y_y', 'y_x'], ['dRa', 'Ra_y', 'Ra_x'],
                ['dR9', 'R9_y', 'R9_x']]
        # 对各个系数进行求平均值
        for i in pras:
            df_cal_data1.loc['校准系数', [i[0]]] = round(float(df_cal_data[i[0]].mean()), 4)
        df_cal_data1.loc['校准系数', 'dlm'] = round(float(df_cal_data['dlm'].mean()), 4)
        df_cal_data1.loc['校准系数', 'dI'] = round(float(df_cal_data['dI'].mean()), 4)
        df_cal_data1.loc['校准系数', 'ITEM_CODE'] = df_cal_data.loc[0, 'ITEM_CODE']
        # print(df_cal_data1)
        return df_cal_data1
    else:
        return df_cal_data


# 验证校准是否有效并计算最终系数，在原有系数上面叠加CF2和CF3
def cal_end_coef(coef_data, cf2_data, cf3_data):
    """
    :param coef_data: 中间系数，数据类型为字典
    :param cf2: cf2数据
    :param cf3: cf3数据
    :return: df_end_coef 最终系数数据
    """
    # 获取未加cf2，cf3的校准系数
    coef = pd.Series(coef_data)
    # 创建加cf2，cf3的校准系数，并进行运算
    end_coef = coef.copy()
    pras = [['dU', 'ForwardVoltage_offset'], ['dx', 'CIEx_offset'],
            ['dy', 'CIEy_offset'], ['dRa', 'CRI_offset'], ['dR9', 'R9_offset']]
    for i in pras:
        end_coef[
            i[0]] = coef[i[0]] + cf2_data.loc[0, i[1]] + cf3_data.loc[0, i[1]]
    end_coef['dlm'] = coef['dlm'] * cf2_data.loc[
        0, 'LuminousFlux_gain'] * cf3_data.loc[0, 'LuminousFlux_gain']
    df_end_coef = end_coef.to_frame().T.rename(index={0: "最终系数"})
    return df_end_coef


# 测试数据加系数运算,统一按y=kx+b进行线性计算
def cal_end_data(test_data, cf_data):
    """
    :param test_data:测试数据
    :param cf_data:cf2 或cf 3
    :return:
    """
    pras = [['ForwardVoltage_V', 'ForwardVoltage_gain', 'ForwardVoltage_offset'], ['CIEx', 'CIEx_gain', 'CIEx_offset'],
            ['CIEy', 'CIEy_gain', 'CIEy_offset'], ['LuminousFlux_lm', 'LuminousFlux_gain', 'LuminousFlux_offset'],
            ['Ra', 'CRI_gain', 'CRI_offset'], ['R9', 'R9_gain', 'R9_offset']]
    cal_data = test_data.copy()
    # 全部安装y=kx+b进行线性计算
    for i in pras:
        cal_data[i[0]] = test_data.loc[:, [i[0]]].apply(
            lambda x: cf_data.loc[0, i[1]] * x + cf_data.loc[0, i[2]])
    return cal_data


# 远方机测试数据直接对数据进行CF2,CF3的叠加(系数叠加仿照cal_end_coef)
def add_cf_data(data, cf2_data, cf3_data):
    # 如果不存在CF2,CF3,不进行叠加
    if cf2_data.empty and cf3_data.empty:
        return data
    # 如果只有CF3,只进行CF3的叠加
    elif cf2_data.empty:
        end_coef = data.copy()
        pras = [['ForwardVoltage_V', 'ForwardVoltage_offset'], ['CIEx', 'CIEx_offset'],
                ['CIEy', 'CIEy_offset'], ['Ra', 'CRI_offset'], ['R9', 'R9_offset']]
        for i in pras:
            end_coef[i[0]] = data[i[0]] + cf3_data.loc[0, i[1]]
        end_coef['LuminousFlux_lm'] = data['LuminousFlux_lm'] * cf3_data.loc[0, 'LuminousFlux_gain']
    # 如果只有CF2,只进行CF2的叠加
    elif cf3_data.empty:
        end_coef = data.copy()
        pras = [['ForwardVoltage_V', 'ForwardVoltage_offset'], ['CIEx', 'CIEx_offset'],
                ['CIEy', 'CIEy_offset'], ['Ra', 'CRI_offset'], ['R9', 'R9_offset']]
        for i in pras:
            end_coef[i[0]] = data[i[0]] + cf2_data.loc[0, i[1]]
        end_coef['LuminousFlux_lm'] = data['LuminousFlux_lm'] * cf2_data.loc[0, 'LuminousFlux_gain']
    # CF2,CF3都存在的情况下,进行CF2,CF3的叠加
    else:
        end_coef = data.copy()
        pras = [['ForwardVoltage_V', 'ForwardVoltage_offset'], ['CIEx', 'CIEx_offset'],
                ['CIEy', 'CIEy_offset'], ['Ra', 'CRI_offset'], ['R9', 'R9_offset']]
        for i in pras:
            end_coef[
                i[0]] = data[i[0]] + cf2_data.loc[0, i[1]] + cf3_data.loc[0, i[1]]
        end_coef['LuminousFlux_lm'] = data['LuminousFlux_lm'] * cf2_data.loc[
            0, 'LuminousFlux_gain'] * cf3_data.loc[0, 'LuminousFlux_gain']
    # 返回加过系数的数据
    return end_coef


def decrease_data(standpart_data, test_data, MoID):
    # 测试数据
    df_test1 = test_data[['TestNO', 'Current_mA', 'ForwardVoltage_V', 'LuminousFlux_lm', 'CIEx', 'CIEy', 'Ra', 'R9']]
    df_test1 = df_test1.rename(columns={'TestNO': 'StandardPartNO'})
    # 标准件数据
    standard_data = standpart_data[['StandardPartNO', 'I_y', 'U_y', 'x_y', 'y_y', 'lm_y', 'Ra_y', 'R9_y']]
    # 拼接起来，再计算差值，存储到数据库
    df_total = pd.merge(df_test1, standard_data, how='left', on=['StandardPartNO'])
    # print(df_total)
    df_total['Current_mA'] = df_total['Current_mA'] - df_total['I_y']
    df_total['ForwardVoltage_V'] = df_total['ForwardVoltage_V'] - df_total['U_y']
    df_total['LuminousFlux_lm'] = (df_total['LuminousFlux_lm'] / df_total['lm_y']) - 1
    df_total['CIEx'] = df_total['CIEx'] - df_total['x_y']
    df_total['CIEy'] = df_total['CIEy'] - df_total['y_y']
    df_total['Ra'] = df_total['Ra'] - df_total['Ra_y']
    df_total['R9'] = df_total['R9'] - df_total['R9_y']
    df_total = df_total[['StandardPartNO', 'Current_mA', 'ForwardVoltage_V', 'LuminousFlux_lm', 'CIEx',
                         'CIEy', 'Ra', 'R9']]
    df_avg = pd.DataFrame(
        columns=['MOID', 'UpdateTime', 'Current_mA', 'ForwardVoltage_V', 'LuminousFlux_lm', 'CIEx',
                 'CIEy', 'Ra', 'R9'])
    # print(df_total['Current_mA'].mean())
    df_avg.loc[1, 'MOID'] = MoID
    df_avg.loc[1, 'UpdateTime'] = datetime.now()
    df_avg.loc[1, 'Current_mA'] = df_total['Current_mA'].mean()
    df_avg.loc[1, 'ForwardVoltage_V'] = df_total['ForwardVoltage_V'].mean()
    df_avg.loc[1, 'LuminousFlux_lm'] = df_total['LuminousFlux_lm'].mean()
    df_avg.loc[1, 'CIEx'] = df_total['CIEx'].mean()
    df_avg.loc[1, 'CIEy'] = df_total['CIEy'].mean()
    df_avg.loc[1, 'Ra'] = df_total['Ra'].mean()
    df_avg.loc[1, 'R9'] = df_total['R9'].mean()
    # print(df_avg)
    return df_avg

# if __name__ == '__main__':
# print(get_item_code('5101-20060387'))
# MY2148-026-1208-MS2SP
# print(get_cf('10105030107',2))
# print(get_cf('10105030107', 3))
# print(get_standpart_data('10105020439'))
# print(get_test_data('G:/Work/DP_ZPConfig_gai/ini/中谱一体机分光数据5101-20070017-0-2167-M.csv.csv',4))
# print(cal_coef(get_standpart_data('10105030107'), get_test_data('5101-20060005-1.csv',6)))
# print(cal_end_coef(cal_coef(get_standpart_data('10105030107'), get_test_data('对标20200521-2.xls',0)).loc['校准系数'].to_dict(),
#              get_cf('10105030107',2),get_cf('10105030107', 3)))
# print(cal_end_data(get_test_data('对标20200521-2.xls',0),get_cf('10105030107',3)))

# test_data = get_test_data('5101-20060005-2222.csv', 6)
# test_data = test_data[test_data['Current_mA'] != 0]
# test_data.dropna(subset=['Current_mA'], inplace=True)
# print(test_data)
